<?php 
class product_plugin_total extends abstract_product_plugin {


    public function getTotal($data = array()) {

        $sql = "SELECT COUNT(DISTINCT p.product_id) AS total"; 	
        $params = array();

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " FROM " . $this->db_prefix . "category_path cp 
                    LEFT JOIN " . $this->db_prefix . "product_to_category p2c 
                    ON (cp.category_id = p2c.category_id)";			
            } else {
                $sql .= " FROM " . $this->db_prefix . "product_to_category p2c";
            }

            if (!empty($data['filter_filter'])) {
                $sql .= " LEFT JOIN " . $this->db_prefix . "product_filter pf 
                    ON (p2c.product_id = pf.product_id) 
                    LEFT JOIN " . $this->db_prefix . "product p 
                    ON (pf.product_id = p.product_id)";
            } else {
                $sql .= " LEFT JOIN " . $this->db_prefix . "product p 
                    ON (p2c.product_id = p.product_id)";
            }
        } else {
            $sql .= " FROM " . $this->db_prefix . "product p";
        }

        $sql .= " LEFT JOIN " . $this->db_prefix . "product_description pd 
            ON (p.product_id = pd.product_id) 
            LEFT JOIN " . $this->db_prefix . "product_to_store p2s 
            ON (p.product_id = p2s.product_id) 
            WHERE pd.language_id = :language_id  
            AND p.status = '1' 
            AND p.date_available <= NOW() 
            AND p2s.store_id = :store_id";

        $params[":language_id"] = $this->language_id;
        $params[":store_id"]    = $this->site_id;

        if (!empty($data['filter_category_id'])) {

            if (!empty($data['filter_sub_category'])) {
                $sql .= " AND cp.path_id = :filter_category_id";
            } else {
                $sql .= " AND p2c.category_id = :filter_category_id";
            }

            $params[":filter_category_id"] = $data['filter_category_id'];

            if (!empty($data['filter_filter'])) {
                $implode = array();

                $filters = explode(',', $data['filter_filter']);

                foreach ($filters as $filter_id) {
                    $implode[] = (int)$filter_id;
                }

                $sql .= " AND pf.filter_id IN (:filter_filter)";
                $params[":filter_filter"] = implode(',', $implode);
            }
        }

        if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
            $sql .= " AND (";

            if (!empty($data['filter_name'])) {
                $implode = array();

                $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name'])));

                $total = count($words) -1;
                for($i = 0; $i <= $total; $i++){
                    $implode[] = "pd.name LIKE :word_" . $i;
                    $params[":word_" . $i] = '%'. $words[$i] . '%';
                }

                if ($implode) {
                    $sql .= " " . implode(" AND ", $implode) . "";
                }

                if (!empty($data['filter_description'])) {
                    $sql .= " OR pd.description LIKE :filter_name";
                    $params[":filter_name"] = '%'. $data['filter_name']. '%';
                }
            }

            if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
                $sql .= " OR ";
            }

            if (!empty($data['filter_tag'])) {
                $sql .= "pd.tag LIKE :filter_tag ";
                $params[":filter_tag"] = '%' .utf8_strtolower($data['filter_tag']).'%';
            }

            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.model) = :filter_name";
                $sql .= " OR LCASE(p.sku) = :filter_name";
                $sql .= " OR LCASE(p.upc) = :filter_name";
                $sql .= " OR LCASE(p.ean) = :filter_name";
                $sql .= " OR LCASE(p.jan) = :filter_name";
                $sql .= " OR LCASE(p.isbn) = :filter_name";
                $sql .= " OR LCASE(p.mpn) = :filter_name";
                $sql .= ")";
                $params[":filter_name"] = utf8_strtolower($data['filter_name']);
            }

        }

        if (!empty($data['filter_manufacturer_id'])) {
            $sql .= " AND p.manufacturer_id = :manufacturer_id";
            $params[":manufacturer_id"] = $data['filter_manufacturer_id'];
        }

        $query = $this->pdo->prepare($sql);
        $query->execute($params);
        $result = $query->fetch(PDO::FETCH_ASSOC);
        return $result['total'];
    }
}
